﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading.Tasks;
using DataTier;
using System.Data;

namespace DataAccessLayer
{
    public class DBStation
    {
        private static SqlCommand dbCmd = null;

        public static void CreateStation(string name, string address, int zip)
        {
            string sql = @"insert into Station values ('" + name + "','" + address + "','" + zip + "')";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void CreateEdge(int fromStation, int toStation, int cost)
        {
            string sql = @"insert into Edge (stationId, toStation, cost) values ('" + fromStation + "','" + toStation + "','" + cost + "')";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void DeleteStation(int stationId)
        {
            string sql = @"delete from Station where stationId ='" + stationId + "'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void DeleteEdgeFrom(int stationId)
        {
            string sql = @"delete from Edge where stationId ='" + stationId + "'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void DeleteEdgeTo(int stationId)
        {
            string sql = @"delete from Edge where toStation ='" + stationId + "'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void UpdateStation(int stationId, string name, string address, int zip)
        {
            string sql = @"update Station set sName = '" + name + "', sAddress = '" + address + "', sZip = '" + zip + "' where stationId ='" + stationId + "'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static Station GetStation(int sId)
        {
            string sql = "select * from Station s, ZipCode z, Battery b where s.sZip = z.zipCode and b.stationId = s.stationId and s.stationId = '" + sId + "'";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Station s;

            if (dbReader.Read())
                s = new Station((int)dbReader["stationId"], dbReader["sName"].ToString(), dbReader["sAddress"].ToString(), (int)dbReader["sZip"], dbReader["city"].ToString());
            else
                s = null;
            dbReader.Close();
            AccessDBSQLClient.Close();
            return s;
        }

        public static List<Station> GetAllStations()
        {
            List<Station> returnList = new List<Station>();
            string sql = "select * from Station s, ZipCode z where s.sZip = z.zipCode";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Station s;

            while (dbReader.Read())
            {
                s = new Station((int)dbReader["stationId"], dbReader["sName"].ToString(), dbReader["sAddress"].ToString(), (int)dbReader["sZip"], dbReader["city"].ToString());
                returnList.Add(s);
            }
            AccessDBSQLClient.Close();
            foreach (Station station in returnList)
            {
                station.Batteries = (DBBattery.GetBatteriesInStation(station.StationId));

            }
            return returnList;
        }

        //public static List<Station> GetStations()
        //{
        //    List<Station> returnList = new List<Station>();
        //    string sql = "select * from Station s, ZipCode z where s.sZip = z.zipCode";
        //    dbCmd = AccessDBSQLClient.GetDbCommand(sql);

        //    IDataReader dbReader;
        //    dbReader = dbCmd.ExecuteReader();

        //    Station s;

        //    while (dbReader.Read())
        //    {
        //        s = new Station((int)dbReader["stationId"], dbReader["sName"].ToString(), dbReader["sAddress"].ToString(), (int)dbReader["sZip"], dbReader["city"].ToString());
        //        returnList.Add(s);
        //    }
        //    AccessDBSQLClient.Close();

        //    return returnList;
        //}

        public static List<Edge> GetAllEdges()
        {
            List<Edge> returnList = new List<Edge>();
            string sql = "select * from Edge";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Edge e;

            while (dbReader.Read())
            {
                e = new Edge((int)dbReader["stationId"], (int)dbReader["toStation"], (int)dbReader["cost"]);
                returnList.Add(e);
            }
            AccessDBSQLClient.Close();

            //foreach (Edge edge in returnList)
            //{
            //    edge.From = DBStation.GetStation(edge.From.StationId);
            //    edge.To = DBStation.GetStation(edge.To.StationId);
            //}

            return returnList;
        }

        //public static List<Station> GetStationsWithEdges()
        //{
        //    List<Station> returnList = new List<Station>();
        //    List<Edge> edgeList = new List<Edge>();
        //    string sql = "select * from Station s, ZipCode z, Edge e where s.sZip = z.zipCode and s.stationId = e.stationId";
        //    dbCmd = AccessDBSQLClient.GetDbCommand(sql);

        //    IDataReader dbReader;
        //    dbReader = dbCmd.ExecuteReader();

        //    Station s;
        //    //Edge e;

        //    //while (dbReader.Read())
        //    //{
        //    //    e = new Edge((int)dbReader["stationId"], (int)dbReader["toStation"], (int)dbReader["cost"]);
        //    //    edgeList.Add(e);
        //    //}
        //    while (dbReader.Read())
        //    {

        //        s = new Station((int)dbReader["stationId"], dbReader["sName"].ToString(), dbReader["sAddress"].ToString(), (int)dbReader["sZip"], dbReader["city"].ToString(), edgeList.Add(new Edge((int)dbReader["stationId"], (int)dbReader["toStation"], (int)dbReader["cost"])));
        //        returnList.Add(s);
        //    }
        //    AccessDBSQLClient.Close();

        //    return returnList;
        //}
    }
}
